﻿using System;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Windows.Forms;
using System.Xml;
using System.Xml.Xsl;
using DefektaiApp.Properties;
using ewal.Msg;
using ewal.Data;

namespace DefektaiApp
{
    public partial class frmRecordsToPrint : Form
    {
        private BindingSource bindingSource;
        private DbDataAdapter dAdapter;
        private DbConnection conn;
        private DataTable dTable;
        private DataView dView;
        //private const string linijosCondition = "(k11*10+k12<=25) AND (k31=1)";
        //private const string nelinijosCondition = "k31>1";
        
        public frmRecordsToPrint()
        {
            InitializeComponent();
        }

        private void frmRecordsToPrint_Load(object sender, EventArgs e)
        {
            doCombos();
            dgvUnprinted.AutoGenerateColumns = false;
            doColumns();
            cmbReportOptions.ComboBox.SelectedIndex = 0;
            query();           
        }

        private void doCombos()
        {
            string sqlSt = "SELECT DISTINCT iteikti_dfId FROM Pranesimai WHERE (atspausdinta=False) AND NOT(iteikti_dfId IS NULL) ORDER BY iteikti_dfId;";
            DataTable dt = DbHelper.FillDataTable(sqlSt);
            DataRow dr = dt.NewRow();
            dr["iteikti_dfId"] = -1;
            dt.Rows.Add(dr);
            cmbDfId.ComboBox.DataSource = dt;
            cmbDfId.ComboBox.ValueMember = "iteikti_dfId";
            cmbDfId.ComboBox.DisplayMember = "iteikti_dfId";
            cmbDfId.ComboBox.SelectedValue = -1;
            
            string [] reportOptions = new string[3] {"visi", "tik žurnale", "tik ne žurnale"};
            cmbReportOptions.ComboBox.DataSource = reportOptions;            
        }

        private void doColumns()
        {
            foreach (DataGridViewColumn column in dgvUnprinted.Columns)
            {
                column.DataPropertyName = column.Name;
            }
        }

        private string setupQueryOptions()
        {
            string queryOptions =  "atspausdinta=FALSE AND ";
            switch (cmbReportOptions.ComboBox.SelectedIndex)
            {
                case 1:
                    queryOptions += "zurnale=TRUE AND ";
                    break;
                case 2:
                    queryOptions += "zurnale=FALSE AND ";
                    break;
                default:
                    queryOptions += ""; 
                    break;
            }

            int selval;
            try
            {
                selval = Convert.ToInt16(cmbDfId.ComboBox.SelectedValue);
            }
            catch
            {
                selval = -1;
            }

            
            if (selval != -1)
            {
                queryOptions += "iteikti_dfId=" + selval.ToString();
            }
            else
            {
                queryOptions += "NOT(iteikti_dfId IS NULL)";
            }
            return queryOptions;
        }

        private void query()
        {
            //Msg.InformationMsg(setupQueryOptions());
            string sqlStr = "SELECT * FROM Pranesimai ";
            sqlStr += "WHERE " + setupQueryOptions() + " ORDER BY iteikti_dfId, iteikti_data, k11, k12, k21, k22, k23, k24, k31, k32, k41, k42, k51;";
            dAdapter = DbHelper.DPFactory.CreateDataAdapter();
            conn = DbHelper.DPFactory.CreateConnection();
            conn.ConnectionString = DbHelper.ConnectionString;
            dAdapter.SelectCommand = DbHelper.DPFactory.CreateCommand();
            dAdapter.SelectCommand.Connection = conn;
            dAdapter.SelectCommand.CommandText = sqlStr;
            dTable = new DataTable();
            dAdapter.Fill(dTable);
            dView = dTable.DefaultView;
            bindingSource = new BindingSource();
            bindingSource.DataSource = dView;
            dgvUnprinted.DataSource = bindingSource;
         }

        private bool createReport(string dfId, bool tik_zurnale)
        {

            string condition = setupQueryOptions();

            //išrenkami visi nespausdinti įrašai, skirti konkrečiam defektoskopui (arba visiems)
            string sqlSt = string.Format("SELECT * FROM Pranesimai WHERE {0} ORDER BY {1};", condition, "iteikti_data, k11, k12, k21, k22, k23, k24, k31, k32, k41, k42, k51");
            DataTable linijosTable = DbHelper.FillDataTable(sqlSt);

            if (linijosTable.Rows.Count == 0) return false;

            // randama pati anksčiausia data, kada defektoskopas tikrins kažkurią iš tų vietų
            sqlSt = string.Format("SELECT MIN(iteikti_data) FROM Pranesimai WHERE {0};", condition);
            string iteikti_iki = ((DateTime)DbHelper.FetchSingleValue(sqlSt)).ToString("yyyy-MM-dd");

            // randama, kuris operatorius eis pirmas, ir sužinomas jo vardas
            sqlSt = string.Format("SELECT vardas, iteikti_data FROM Pranesimai INNER JOIN Operatoriai ON Pranesimai.iteikti_operId=Operatoriai.id WHERE {0} ORDER BY Pranesimai.iteikti_data;", condition);
            DataTable dt = DbHelper.FillDataTable(sqlSt);
            string iteiktiOperatoriui = dt.Rows[0]["vardas"].ToString();            

            string outputFileName = string.Format("Vietos {0} {1}.html", dfId, iteikti_iki);
            sfdOutputReport.FileName = outputFileName;
            sfdOutputReport.InitialDirectory = Properties.UserSettings.Default.OutputPath;
            sfdOutputReport.Filter = "HTML failai (*.htm;*.html)|*htm;*.html|Visi failai (*.*)|*.*";
            if (sfdOutputReport.ShowDialog() == DialogResult.OK)
            {
                outputFileName = sfdOutputReport.FileName;
                Properties.UserSettings.Default.OutputPath = Path.GetDirectoryName(sfdOutputReport.FileName);
            }
            else return false;

            XslCompiledTransform xslt = new XslCompiledTransform();
            if (tik_zurnale)
            {
                xslt.Load("defect_zurnale_template.xsl");
            }
            else
            {
                xslt.Load("defect_template.xsl");
            }

            XmlDocument xml = makeXml(linijosTable, dfId, iteikti_iki, iteiktiOperatoriui);

            // Execute the transform and output the results to a file.
            try
            {
                xslt.Transform(new XmlNodeReader(xml), XmlWriter.Create(outputFileName));
                Msg.InformationMsg("Išsaugota: " + outputFileName);
            }
            catch (Exception ex)
            {
                Msg.ErrorMsg(ex.Message);
                return false;
            }

            return true;
        }

        private void btnCreateReports_Click(object sender, EventArgs e)
        {
            string dfId = cmbDfId.ComboBox.SelectedValue.ToString();
            bool tikZurnale = cmbReportOptions.ComboBox.SelectedValue == "tik žurnale";
            if (createReport(dfId, tikZurnale))
            {
                string today = DbHelper.FormatDateValue(DateTime.Today);
                string sqlSt = string.Format("UPDATE Pranesimai SET atspausdinta=True, spausdinta_data={0} WHERE {1};", today, setupQueryOptions());
                DbHelper.ExecuteNonQuery(sqlSt);
                query();
                doCombos();
            }
        }

        private XmlDocument makeXml(DataTable tbl, string defektoskopo_kodas, string iteikti_iki_datos, string iteikti_operatorius)
        {
            XmlDocument xmlDoc = new XmlDocument();

            XmlDeclaration declaration = xmlDoc.CreateXmlDeclaration("1.0", null, null);
            xmlDoc.AppendChild(declaration);
            
            XmlElement root = xmlDoc.CreateElement("pranesimas");
            xmlDoc.AppendChild(root);

            XmlElement elem;

            elem = xmlDoc.CreateElement("defektoskopo_kodas");
            elem.InnerText = defektoskopo_kodas;
            root.AppendChild(elem);

            elem = xmlDoc.CreateElement("iteikti_iki_datos");
            elem.InnerText = iteikti_iki_datos;
            root.AppendChild(elem);

            elem = xmlDoc.CreateElement("iteikti_operatoriui_vardu");
            elem.InnerText = iteikti_operatorius;
            root.AppendChild(elem);            

            XmlElement vietos = xmlDoc.CreateElement("itartinos_vietos");
            fillIrasas(vietos, tbl);
            root.AppendChild(vietos);                

            return xmlDoc;
        }

        private void fillIrasas(XmlElement parent, DataTable table)
        {
            string[] vKodasColumns = { "k11", "k12", "k21", "k22", "k23", "k24", "k31", "k32", "k41", "k42", "k51" };
            string[] defektasColumns = { "d0", "d70p", "d55ad", "d55an", "d42p", "d42a", "d70a", "d55pd", "pastaba" };
            string[] datosColumns = { "rasta_data" };
            string[] restColumns = { "defFailoNr", "rasta_dfId", "id" };

            XmlDocument doc = parent.OwnerDocument;
            XmlElement elem;

            foreach (DataRow row in table.Rows)
            {
                XmlElement record = doc.CreateElement("irasas");
                record.SetAttribute("id", row["id"].ToString());

                XmlElement vkodas = doc.CreateElement("vietos_kodas");
                foreach (string column in vKodasColumns)
                {
                    elem = doc.CreateElement(column);
                    elem.InnerText = row[column].ToString();
                    vkodas.AppendChild(elem);
                }
                record.AppendChild(vkodas);

                XmlElement defektas = doc.CreateElement("defekto_aprasymas");
                foreach (string column in defektasColumns)
                {
                    elem = doc.CreateElement(column);
                    elem.InnerText = row[column].ToString();
                    defektas.AppendChild(elem);
                }
                record.AppendChild(defektas);

                foreach (string column in restColumns)
                {
                    elem = doc.CreateElement(column);
                    elem.InnerText = row[column].ToString();
                    record.AppendChild(elem);
                }

                foreach (string column in datosColumns)
                {
                    elem = doc.CreateElement(column);
                    elem.InnerText = ((DateTime)row[column]).ToString("yyyy-MM-dd");
                    record.AppendChild(elem);
                }

                parent.AppendChild(record);
            }
        }

        private void cmbReportOptions_SelectedIndexChanged(object sender, EventArgs e)
        {
            query();
        }

        private void tscmbDfId_SelectedIndexChanged(object sender, EventArgs e)
        {
            query();
        }

    }
}
